﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace SQLTask2
{
    public partial class SearchForm : Form
    {
        private DataTable ds = null;
        private MySqlConnection pConn;
        private MySqlDataAdapter da = null;

        public SearchForm(MySqlConnection conn)
        {
            InitializeComponent();
            pConn = conn;
            ds = new DataTable();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string query = @"SELECT t.Transaction_ID AS `ID`,
                                    b.Name AS `Банк`,
                                    t.Time AS `Время`,
                                    t.Date AS `Дата`,
                                    t.Quantity AS `Кол-во`,
                                    r.Buy AS `Покупка`,
                                    r.Sell AS `Продажа`,
							        f.Name AS `В1`,
							        s.Name AS `В2`
                             FROM transactions t 
                             JOIN rates r ON t.Rate_ID=r.Rate_ID ";
            if (textBox1.Text != "")
            {
                switch (comboBox2.SelectedIndex)
                {
                    case 0: query += "JOIN banks b ON b.Name='"+textBox1.Text+"' "; 
                            break;
                    case 1: query += "JOIN banks b ON b.Name LIKE '" + textBox1.Text + "%' ";
                            break;
                    case 2: query += "JOIN banks b ON b.Name LIKE '%" + textBox1.Text + "' ";
                            break;
                    case 3: query += "JOIN banks b ON b.Name LIKE '%" + textBox1.Text + "%' ";
                            break;
                }
            }
            else
            {
                query += "JOIN banks b ON t.Bank_ID=b.Bank_ID ";
            }

            if (textBox2.Text != "")
            {
                switch (comboBox3.SelectedIndex)
                {
                    case 0: query += "JOIN (SELECT Currency_ID, Name from currencies where Name='" + textBox2.Text + "') AS f ON f.Currency_ID=r.F_Currency_ID ";
                        break;
                    case 1: query += "JOIN (SELECT Currency_ID, Name from currencies where Name LIKE '" + textBox2.Text + "%') AS f ON f.Currency_ID=r.F_Currency_ID ";
                        break;
                    case 2: query += "JOIN (SELECT Currency_ID, Name from currencies where Name LIKE '%" + textBox2.Text + "') AS f ON f.Currency_ID=r.F_Currency_ID ";
                        break;
                    case 3: query += "JOIN (SELECT Currency_ID, Name from currencies where Name LIKE '%" + textBox2.Text + "%') AS f ON f.Currency_ID=r.F_Currency_ID ";
                        break;
                }
            }
            else
            {
                query += "JOIN currencies f ON r.F_Currency_ID=f.Currency_ID ";
            }

            if (textBox4.Text != "")
            {
                switch (comboBox4.SelectedIndex)
                {
                    case 0: query += "JOIN (SELECT Currency_ID, Name from currencies where Name='" + textBox4.Text + "') AS s ON s.Currency_ID=r.S_Currency_ID ";
                        break;
                    case 1: query += "JOIN (SELECT Currency_ID, Name from currencies where Name LIKE '" + textBox4.Text + "%') AS s ON s.Currency_ID=r.S_Currency_ID ";
                        break;
                    case 2: query += "JOIN (SELECT Currency_ID, Name from currencies where Name LIKE '%" + textBox4.Text + "') AS s ON s.Currency_ID=r.S_Currency_ID ";
                        break;
                    case 3: query += "JOIN (SELECT Currency_ID, Name from currencies where Name LIKE '%" + textBox4.Text + "%') AS s ON s.Currency_ID=r.S_Currency_ID ";
                        break;
                }
            }
            else
            {
                query += "JOIN currencies s ON r.S_Currency_ID=s.Currency_ID ";
            }

            if (textBox3.Text != "")
            {
                query += "WHERE t.Quantity" + comboBox1.Text + "'" + textBox3.Text + "'";
            }


            textBox5.Text = query;
            try
            {
                da = new MySqlDataAdapter(query, pConn);
                dataGridView1.DataSource = ds;
                UpdateMainTable();
            }
            catch (MySqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message);
            }
        }

        private void UpdateMainTable()
        {
            try
            {
                ds.Clear();
                da.Fill(ds);
                dataGridView1.Columns[0].Width = 35;
                dataGridView1.Refresh();
            }
            catch (MySqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message);
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                da = new MySqlDataAdapter(textBox5.Text, pConn);
                dataGridView1.DataSource = ds;
                UpdateMainTable();
            }
            catch (MySqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message);
            }
        }
    }
}
